﻿using DevExpress.XtraEditors;
using Lamex.Base;
using Lamex.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.mLabor.Common
{
   public class GridViewSaveHelper
    {
        public static bool FindExcelToDataBase(DataTable dt, string tableName)
        {

            try
            {

                DataTable dttemp = Lamex.Base.BaseBuildSql.GetDataTableBySql(" select * from " + tableName + " where 1!=1 ");
                List<string> FieldList = new List<string>();
                List<string> removeList = new List<string>();
                foreach (DataColumn col in dt.Columns)
                {
                    FieldList.Add(col.ColumnName);
                }
               
                foreach (DataColumn col in dttemp.Columns)
                {
                    string str = FieldList.Where(s=>s.Contains(col.ColumnName)).FirstOrDefault();
                    if (string.IsNullOrEmpty(str))
                        removeList.Add(col.ColumnName);
                }
                //remove haven't use columns
                foreach (string s in removeList)
                {
                    dttemp.Columns.Remove(s);
                }
                DataTable dtSave = new DataTable();
                dt.TableName = tableName;
                string sql = "insert into " + tableName + "( ";
                string sql2 = "select ";
                string field = "";
                string field2 = "";
                DataTable dtcol = BaseBuildSql.GetSysColunmNameList(tableName);

                List<string> keyLst = new List<string>();
                List<string> keyLstStr = new List<string>();
                bool IsKeyCheck = false;
                bool IsAutoKey = false;
                foreach (DataRow dr in dtcol.Rows)
                {
                    dtcol.TableName = dr["table_name"].ToString();
                    if (dr["keyname"].ToString().ToLower() == "key")
                    {
                        
                        DataTable IsCheckAuto = BaseBuildSql.GetDataTableBySql(@"SELECT * FROM INFORMATION_SCHEMA.columns
                        WHERE TABLE_NAME = '" + tableName + @"' AND COLUMNPROPERTY(OBJECT_ID('" + tableName + @"'), COLUMN_NAME, 'IsIdentity') = 1
                        and COLUMN_NAME = '" + dr["keyname"].ToString().ToLower() + "'");
                        if (IsCheckAuto.Rows.Count != 0)
                        {
                            keyLst.Add(dr["column_name"].ToString().ToLower());
                            IsAutoKey = true;
                        }
                        keyLstStr.Add(dr["column_name"].ToString().ToLower());
                        IsKeyCheck = true;
                    }
                    int m = 0;
                }
                foreach (DataColumn d in dt.Columns)
                {
                    field += d.ColumnName+ ",";
                    field2 += d.ColumnName + ",";
                }
                field = field.Trim(',');
                field2 = field2.Trim(',');
                sql += field;
                sql2 += field2;

                sql += " ) ";
                sql2 += " from #" + tableName + "";
                sql = sql + sql2;
                if (string.IsNullOrEmpty(field))
                {
                    XtraMessageBox.Show("【gridview列名不规范】");
                    return false;
                }
                int iReRow = 0;
                int iInsRow = 0;
                int iTotal = 0;
                //List<string> lst = new List<string>();
                List<string> colNamelist = new List<string>();
                
                //lst.Clear();
                //removeList.Clear();
                //foreach (DataColumn col in dttemp.Columns)
                //{
                //    lst.Add(col.ColumnName);

                //}
               
                DataTable dtInset = dttemp.Clone();
                DataTable dtSuccess = dttemp.Clone();
                dtInset.Columns.Add("Status");
                //start check if data have been in database
                if (!(IsKeyCheck && IsAutoKey))
                {

                    if (dt.Rows.Count != 0)
                    {

                        foreach (DataRow dr in dt.Rows)
                        {
                            DataRow dtrow = dtSuccess.NewRow();
                            string strWhere = "";
                            DataRow drNew = dtInset.NewRow();
                            DataRow drInsertRow = dtSuccess.NewRow();
                            foreach (string s in keyLstStr)
                            {
                                foreach (DataColumn col in dttemp.Columns)
                                {
                                    if (s.ToLower() == col.ColumnName.ToLower())
                                        strWhere += "and " + s + " = '" + dr[s] + "'";
                                }
                            }
         
                            DataTable dtest = BaseBuildSql.GetDataTableBySql("Select * from " + tableName + " where 1=1 " + strWhere);
                            if (dtest.Rows.Count > 0)
                            {
                                iReRow++;
                                foreach (DataColumn col in dt.Columns)
                                {
                                    drNew[col.ColumnName] = dr[col.ColumnName];
                                    drInsertRow[col.ColumnName] = dr[col.ColumnName];
                                }
                                //drNew["Status"] = "数据已存在";
                                dtSuccess.Rows.Add(drInsertRow);
                                // Lamex.Common.DataBaseHelper_41.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                                BaseBuildSql.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                            }
                            else
                            {
                                foreach (DataColumn col in dt.Columns)
                                {
                                    drInsertRow[col.ColumnName] = dr[col.ColumnName];
                                }
                                dtSuccess.Rows.Add(drInsertRow);
                            }

                            //dtInset.Rows.Add(drNew);
                        }

                        iInsRow = dtInset.Rows.Count;
                        dtSuccess.TableName = tableName;
                        DataSet ds = new DataSet();
                        if (dtSuccess.Rows.Count != 0)
                        {
                            ds.Tables.Add(dtSuccess);
                            string errMsg = "";
                            
                            if (BaseBuildSql.ExecuteInsertBulkData("select  " + field + "  into #" + tableName + " from  " + tableName + " where 1!=1", ds, sql, ref errMsg))
                            {
                                XtraMessageBox.Show("保存成功！");

                            }
                            else
                            {
                                XtraMessageBox.Show(errMsg+ "保存失败");
                            }
                        }
                    }
                }
                else
                {
                    foreach (DataRow dr in dt.Rows)
                    {

                        string strWhere = "";
                        DataRow drNew = dtInset.NewRow();
                        DataRow drInsertRow = dtSuccess.NewRow();
                        foreach (string s in keyLst)
                        {
                            strWhere += "and " + s + " = '" + dr[s] + "'";
                        }
                        
                        DataTable dtest = BaseBuildSql.GetDataTableBySql("Select * from " + tableName + " where 1=1 " + strWhere);
                        if (dtest.Rows.Count > 0)
                        {
                            iReRow++;
                            foreach (DataColumn col in dt.Columns)
                            {
                                drNew[col.ColumnName] = dr[col.ColumnName];
                                drInsertRow[col.ColumnName] = dr[col.ColumnName];
                            }
                            //drNew["Status"] = "数据已存在";
                            dtSuccess.Rows.Add(drInsertRow);
                            BaseBuildSql.ExecuteNonQuery("delete  from " + tableName + " where 1=1 " + strWhere);
                        }
                        else
                        {
                            foreach (DataColumn col in dt.Columns)
                            {
                                drNew[col.ColumnName] = dr[col.ColumnName];
                            }

                            //drNew["Status"] = "成功";
                            drInsertRow.ItemArray = dr.ItemArray;
                            dtSuccess.Rows.Add(drInsertRow);
                        }

                        dtInset.Rows.Add(drNew);
                    }
                    DataSet ds = new DataSet();
                    if (dtSuccess.Rows.Count != 0)
                    {
                        ds.Tables.Add(dtSuccess);
                        string errMsg = "";
                       
                        if (BaseBuildSql.ExecuteInsertBulkData("select  " + field + "  into #" + tableName + " from  " + tableName + " where 1!=1", ds, sql, ref errMsg))
                        {
                            XtraMessageBox.Show("保存成功");
                        }
                        else
                        {
                            XtraMessageBox.Show(errMsg + "保存失败");
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        
    }
}
